package xmpp.client.api.user;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import xmpp.client.api.bd.BD;
import xmpp.client.api.bd.XMPPExceptionSQL;

/**
 *
 * @author Miguel Gonzalez - Juanan Mangudo - Telecor
 */
public class UsersXMPP {
    
    private static final String LOAD_USER="SELECT name, email, creationDate, modificationDate, plainPassword, encryptedPassword FROM ofUser WHERE username=?";
    private static final String USER_COUNT="SELECT count(*) FROM ofUser";
    private static final String FRIEND_COUNT="SELECT count(*) FROM ofRoster WHERE username=?";
    private static final String THERE_ARE_USERS="SELECT username FROM ofUser WHERE username=?";
    private static final String WHO_IS_MY_FRIEND="SELECT jig FROM ofRoster WHERE username=?";
    private static final String INSERT_USER="INSERT INTO ofUser (username,plainPassword,encryptedPassword,name,email,creationDate,modificationDate) " +
            "VALUES (?,?,?,?,?,?,?)";
    private static final String DELETE_USER_PROPS="DELETE FROM ofUserProp WHERE username=?";
    private static final String DELETE_USER="DELETE FROM ofUser WHERE username=?";
    private static final String DELETE_USER_OF_GROUP="DELETE FROM ofGroupUser WHERE username=?";
    private static final boolean IS_READ_ONLY = false;

    private BD bd;
    private Connection connection = null;
    
    public UsersXMPP() throws XMPPExceptionSQL {
        bd = new BD();
    }
    
    public void createUser(UserXMPP user) throws XMPPExceptionSQL {
        Connection con=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        System.out.println("ENTRO EN CREATEUSER");
        System.out.println(user.getUserName());
        try
        {
            if (!userExists(user.getUserName()))
            {
                //Corregir estos errores del open connection
                //bd.openConnection(userName, passwordName, host, bdName);
                //con = bd.getConection();
                pstmt = (PreparedStatement)con.prepareStatement(INSERT_USER);
                pstmt.setString(1, user.getUserName());
                pstmt.setString(2, null);
                pstmt.setString(3, user.getPassword());
                pstmt.setString(4, user.getName());
                pstmt.setString(5, user.getEmail());
                pstmt.setString(6, user.getCreationDate());
                pstmt.setString(7, user.getModificationDate());   
                pstmt.executeUpdate();
            }
            else 
            {
                System.out.println("Error, el usuario ya existe");
            }
        }
        catch(Exception e)
        {
            System.out.println("Error");
            System.out.println(e);
        }
         finally {
            try {

                if(pstmt != null) {
                    pstmt.close();
                }

                if(rs != null) {
                    rs.close();
                }
            } catch (SQLException sqle) {
                throw new XMPPExceptionSQL(sqle);
            }            
        }
    }
    
    //public in deleteUser(String userName)
    public int deleteUser(String userName) {
      Connection con=null;
      PreparedStatement pstmt=null;
      try
      {
          con = bd.getConection();
          pstmt = (PreparedStatement)con.prepareStatement(DELETE_USER_PROPS);
          pstmt.setString(1,userName);
          pstmt.execute();
          pstmt=(PreparedStatement)con.prepareStatement(DELETE_USER);
          pstmt.setString(1, userName);
          pstmt.execute();
          pstmt=(PreparedStatement)con.prepareStatement(DELETE_USER_OF_GROUP);
          pstmt.setString(1, userName);
          pstmt.executeUpdate();  
          if(pstmt != null) 
          {
              pstmt.close();
          }
      }
      catch(Exception e)
      {
          System.out.println("Ocurrió un error");
      }
      return 0;
    }
    
    public int vinculeUser(String idUserOne, String idUserTwo) {
        return 0;
    }
    
    
    /*public ArrayList<UserXMPP> listUsers() throws XMPPExceptionSQL {
        ArrayList<UserXMPP> usersXMPP = new ArrayList<UserXMPP>();
        
        //Abrimos la conexión
        //Obtenemos la conexión
        Connection connection = bd.getConection();
        
        ResultSet rs = null;
        try {
            //Preparamos la conexulta
            Statement stmt = (Statement) connection.createStatement();
            
            //Lanzamos la consulta
            rs = stmt.executeQuery("SELECT `username` , `name` , `email` , `encryptedPassword`, `creationDate` ,"
                    + " `modificationDate`FROM `ofUser`");
            
            //Analizamos la consulta
            while(rs.next()) {
                usersXMPP.add(new UserXMPP(rs.getString("username"), rs.getString("name"),
                        rs.getString("email"), rs.getString("encryptedPassword"),rs.getString("creationDate"), rs.getString("modificationDate")));
            }
        } catch (SQLException ex) {
            throw new XMPPExceptionSQL(ex);
        }
        
        return usersXMPP;
    }*/
    
    public boolean userExists(String userName) throws XMPPExceptionSQL
    {
      Connection con=null;
      PreparedStatement pstmt=null;
      ResultSet rs=null;
      try
      {
      pstmt = (PreparedStatement)con.prepareStatement(THERE_ARE_USERS);
      pstmt.setString(1, userName);
      rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                return true;
            }
            else return false;
      }
      catch(Exception e)
      {
          
      }
              finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
      return false;
    }
    
    public UserXMPP getUser(String userName) throws XMPPExceptionSQL
    {
      Connection con=null;
      PreparedStatement pstmt=null;
      ResultSet rs=null;
      UserXMPP user = null;
      try
      {
            pstmt = (PreparedStatement)con.prepareStatement(LOAD_USER);
            pstmt.setString(1,userName);
            rs=pstmt.executeQuery();
            String username=userName;
            String email=rs.getString(2);
            String creationDate=rs.getString(3);
            String modificationDate=rs.getString(4);
            String plainPassword=rs.getString(5);
            String encryptedPassword=rs.getString(6);
            String name=rs.getString(1);
            user=new UserXMPP(userName, name, email, plainPassword, encryptedPassword, creationDate, modificationDate);
      }
      catch(Exception e)
      {
          
      }
              finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return user;
    }
    
    public void setConnection(Connection connection) {
        bd.setConnection(connection);
    }
    
    public ArrayList<String> getFriends(String userName) throws XMPPExceptionSQL
    {
        ArrayList<String> listaAmigos=new ArrayList<String>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = (PreparedStatement) connection.prepareStatement(WHO_IS_MY_FRIEND);
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String listaGrupos = rs.getString(1);

                String[] grupos = listaGrupos.split(",");
                for(String grupo : grupos) {
                    listaAmigos.add(grupo);
                }
            }
        }
        catch (SQLException e) {
            throw new XMPPExceptionSQL(e);
        }
        finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }

                if(rs != null) {
                    rs.close();
                }
                
            } catch (SQLException sqle) {
                throw new XMPPExceptionSQL(sqle);
            }            
        }
           return listaAmigos;
    }
    
    //public addFriend(String userName, String userNameFriend) throws...
    
    //public delFriend(String userName, String userNameFrined) throws...
    
    //public boolean isFriend(String userName, String userNameFriend) throws...
    
    public int countFriends(String userName) throws XMPPExceptionSQL
    {
        int friendsNumber=0;
                PreparedStatement pstmt = null;
        ResultSet rs = null;
        try 
        {
            
            pstmt = (PreparedStatement) connection.prepareStatement(FRIEND_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                friendsNumber = rs.getInt(1);
            }
        }
        catch (SQLException e) 
        {
            throw new XMPPExceptionSQL(e);
        }
        finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return friendsNumber;
    }
    
    public int countUsers() throws XMPPExceptionSQL
    {
        int usersNumber=0;
                PreparedStatement pstmt = null;
        ResultSet rs = null;
        try 
        {
            
            pstmt = (PreparedStatement) connection.prepareStatement(USER_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                usersNumber = rs.getInt(1);
            }
        }
        catch (SQLException e) 
        {
            throw new XMPPExceptionSQL(e);
        }
        finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return usersNumber;
    }
    
    
    
    public void openConnection(String userName, String passwordName, String host, String bdName) throws XMPPExceptionSQL 
    {
        bd.openConnection(userName, passwordName, host, bdName);
        connection = bd.getConection();
    }
    
    public void closeConnection() throws XMPPExceptionSQL 
    {
            bd.closeConnection();       
    }
}
